#!/bin/sh
#抽取trip_statistics_temp故障数据到trip_statistics_temp表中,单车单故障每天只计算一次，最早出现和最晚出现时间分别为开始，终止时间
CUR_DIR=$(cd "$(dirname "${BASH_SOURCE-$0}")"; pwd)
UP_DIR=$(dirname $(dirname $(dirname "$PWD")))
echo ${UP_DIR}
source ${UP_DIR}/system_env.sh


START_TIME=$(date +%s)
hive_db=${hive_db}

if [ -z "$1" ];then
  day=$(date -d "1 days ago" +%Y%m%d)
  day_ago=$(date -d "2 days ago" +%Y%m%d)
else
  day=$1
  day_ago=$(date -d "yesterday $1" +%Y%m%d)
fi





sql_suffix=$(cat <<EOF
 insert overwrite table ${hive_db}.trip_statistics_temp partition(day)
select s.uid   uid,
       s.tid   tid,
       e.trip_id  trip_id,
       s.route_start_time   route_start_time,
	   s.route_start_lc   route_start_lc,
	   s.route_start_latitude   route_start_latitude,
	   s.route_start_longitude   route_start_longitude,
	   s.route_start_height   route_start_height ,
	 s.route_start_direction route_start_direction ,
	 s.route_start_mileage   route_start_mileage ,
	 s.route_start_fuel route_start_fuel,
	 e.route_end_time  route_end_time,
	 e.route_end_lc  route_end_lc,
	 e.route_end_latitude route_end_latitude,
	 e.route_end_longitude route_end_longitude,
	 e.route_end_height  route_end_height,
	 e.route_end_direction route_end_direction,
	 e.route_end_mileage route_end_mileage,
	 e.route_end_fuel  route_end_fuel,
	 (s.route_integral_mileage + e.route_integral_mileage)   route_integral_mileage,
	 (s.route_integral_fuel + e.route_integral_fuel)   route_integral_fuel,
	 (s.trip_power + e.trip_power)   trip_power,
	 s.stype   stype,
	 (s.trip_mileage + e.trip_mileage)   trip_mileage,
	 (s.trip_fuel + e.trip_fuel)   trip_fuel,
	 (s.trip_duration + e.trip_duration)   trip_duration,
	 e.route_stop_flag  route_stop_flag,
	 s.cross_day_flag  cross_day_flag,
	 e.force_end_flag  force_end_flag,
	 (case when s.max_speed >= e.max_speed  then s.max_speed else e.max_speed end)   max_speed,
	 (case when s.min_speed >= e.min_speed  then e.min_speed else s.min_speed end)   min_speed,
     (case when s.max_useful_speed >= e.max_useful_speed  then s.max_useful_speed else e.max_useful_speed end)   max_useful_speed,
	 (case when s.min_useful_speed >= e.min_useful_speed  then e.min_useful_speed else s.min_useful_speed end)   min_useful_speed,
	 (s.in_gear_coast_mileage + e.in_gear_coast_mileage)   in_gear_coast_mileage,
	 (s.in_gear_coast_duration + e.in_gear_coast_duration)   in_gear_coast_duration,
	 (s.in_gear_coast_number + e.in_gear_coast_number)   in_gear_coast_number,
	 (s.brake_number   + e.brake_number    )    brake_number   ,
	 (s.brake_mileage  + e.brake_mileage   )    brake_mileage  ,
	 (s.brake_duration + e.brake_duration  )    brake_duration ,
	 (s.vehicle_estimated_load + e.vehicle_estimated_load)/2   vehicle_estimated_load,
	 (s.neutral_gear_coast_duration +e.neutral_gear_coast_duration )   neutral_gear_coast_duration  ,
	 (s.neutral_gear_coast_fuel     +e.neutral_gear_coast_fuel     )   neutral_gear_coast_fuel      ,
	 (s.neutral_gear_coast_mileage  +e.neutral_gear_coast_mileage  )   neutral_gear_coast_mileage   ,
	 (s.neutral_gear_coast_number   +e.neutral_gear_coast_number   )   neutral_gear_coast_number    ,
	 (s.idle_fuel                   +e.idle_fuel                   )   idle_fuel                    ,
	 (s.idle_duration               +e.idle_duration               )   idle_duration                ,
	 (s.idle_number                 +e.idle_number                 )   idle_number                  ,
	 (s.parking_power_on_duration   +e.parking_power_on_duration   )   parking_power_on_duration    ,
	 (s.city_driving_mileage        +e.city_driving_mileage        )   city_driving_mileage         ,
	 (s.city_driving_duration       +e.city_driving_duration       )   city_driving_duration        ,
	 (s.city_driving_fuel           +e.city_driving_fuel           )   city_driving_fuel            ,
	 (s.rural_driving_mileage       +e.rural_driving_mileage       )   rural_driving_mileage        ,
	 (s.rural_driving_duration      +e.rural_driving_duration      )   rural_driving_duration       ,
	 (s.rural_driving_fuel          +e.rural_driving_fuel          )   rural_driving_fuel           ,
	 (s.eco_driving_mileage         +e.eco_driving_mileage         )   eco_driving_mileage          ,
	 (s.eco_driving_duration        +e.eco_driving_duration        )   eco_driving_duration         ,
	 (s.eco_driving_fuel            +e.eco_driving_fuel            )   eco_driving_fuel             ,
	 (s.up_slope_driving_mileage    +e.up_slope_driving_mileage    )   up_slope_driving_mileage     ,
	 (s.up_slope_driving_duration   +e.up_slope_driving_duration   )   up_slope_driving_duration    ,
	 (s.up_slope_driving_fuel       +e.up_slope_driving_fuel       )   up_slope_driving_fuel        ,
	 (s.down_slope_driving_mileage  +e.down_slope_driving_mileage  )   down_slope_driving_mileage   ,
	 (s.down_slope_driving_duration +e.down_slope_driving_duration )   down_slope_driving_duration  ,
	 (s.down_slope_driving_fuel     +e.down_slope_driving_fuel     )   down_slope_driving_fuel      ,
	 (s.level_road_driving_mileage  +e.level_road_driving_mileage  )   level_road_driving_mileage   ,
	 (s.level_road_driving_duration +e.level_road_driving_duration )   level_road_driving_duration  ,
	 (s.level_road_driving_fuel     +e.level_road_driving_fuel     )   level_road_driving_fuel      ,
	 (s.cruise_mileage              +e.cruise_mileage              )   cruise_mileage               ,
	 (s.cruise_duration             +e.cruise_duration             )   cruise_duration              ,
	 (s.cruise_fuel                 +e.cruise_fuel                 )   cruise_fuel                  ,
	 (s.shift_gears_number          +e.shift_gears_number          )   shift_gears_number           ,
	 (s.sharp_down_speed_duration   +e.sharp_down_speed_duration   )   sharp_down_speed_duration    ,
	 (s.sharp_down_speed_mileage    +e.sharp_down_speed_mileage    )   sharp_down_speed_mileage     ,
	 (s.sharp_down_speed_number     +e.sharp_down_speed_number     )   sharp_down_speed_number      ,
	 (s.sharp_up_speed_duration     +e.sharp_up_speed_duration     )   sharp_up_speed_duration      ,
	 (s.sharp_up_speed_mileage      +e.sharp_up_speed_mileage      )   sharp_up_speed_mileage       ,
	 (s.sharp_up_speed_number       +e.sharp_up_speed_number       )   sharp_up_speed_number        ,
	 (s.sharp_up_speed_fuel         +e.sharp_up_speed_fuel         )   sharp_up_speed_fuel          ,
	 (s.sharp_step_on_gas_mileage   +e.sharp_step_on_gas_mileage   )   sharp_step_on_gas_mileage    ,
	 (s.sharp_step_on_gas_duration  +e.sharp_step_on_gas_duration  )   sharp_step_on_gas_duration   ,
	 (s.sharp_step_on_gas_fuel      +e.sharp_step_on_gas_fuel      )   sharp_step_on_gas_fuel       ,
	 (s.sharp_step_on_gas_number    +e.sharp_step_on_gas_number    )   sharp_step_on_gas_number     ,
	 (s.vehicle_park_number         +e.vehicle_park_number         )   vehicle_park_number          ,
	 (s.vehicle_start_number        +e.vehicle_start_number        )   vehicle_start_number         ,
	 (s.vehicle_start_mileage       +e.vehicle_start_mileage       )   vehicle_start_mileage        ,
	 (s.vehicle_start_duration      +e.vehicle_start_duration      )   vehicle_start_duration       ,
	 (s.vehicle_start_fuel          +e.vehicle_start_fuel          )   vehicle_start_fuel           ,
	 (s.vehicle_cold_start_number   +e.vehicle_cold_start_number   )   vehicle_cold_start_number    ,
	 (s.vehicle_cold_start_mileage  +e.vehicle_cold_start_mileage  )   vehicle_cold_start_mileage   ,
	 (s.vehicle_cold_start_duration +e.vehicle_cold_start_duration )   vehicle_cold_start_duration  ,
	 (s.vehicle_cold_start_fuel     +e.vehicle_cold_start_fuel     )   vehicle_cold_start_fuel      ,
	 (s.charge_fuel_percent         +e.charge_fuel_percent         )   charge_fuel_percent          ,
	 (s.charge_fuel                 +e.charge_fuel                 )   charge_fuel                  ,
	 (s.leak_fuel_percent           +e.leak_fuel_percent           )   leak_fuel_percent            ,
	 (s.leak_fuel                   +e.leak_fuel                   )   leak_fuel                    ,
	 (s.gas_stability_number        +e.gas_stability_number        )   gas_stability_number         ,
	 (s.gas_stability_duration      +e.gas_stability_duration      )   gas_stability_duration       ,
	 (s.gas_open_percent_sum        +e.gas_open_percent_sum        )   gas_open_percent_sum         ,
	 (s.gas_open_percent_count      +e.gas_open_percent_count      )   gas_open_percent_count       ,
	 (s.speed_range01_mileage       +e.speed_range01_mileage       )   speed_range01_mileage        ,
	 (s.speed_range01_duration      +e.speed_range01_duration      )   speed_range01_duration       ,
	 (s.speed_range01_fuel          +e.speed_range01_fuel          )   speed_range01_fuel           ,
	 (s.speed_range02_mileage       +e.speed_range02_mileage       )   speed_range02_mileage        ,
	 (s.speed_range02_duration      +e.speed_range02_duration      )   speed_range02_duration       ,
	 (s.speed_range02_fuel          +e.speed_range02_fuel          )   speed_range02_fuel           ,
	 (s.speed_range03_mileage       +e.speed_range03_mileage       )   speed_range03_mileage        ,
	 (s.speed_range03_duration      +e.speed_range03_duration      )   speed_range03_duration       ,
	 (s.speed_range03_fuel          +e.speed_range03_fuel          )   speed_range03_fuel           ,
	 (s.speed_range04_mileage       +e.speed_range04_mileage       )   speed_range04_mileage        ,
	 (s.speed_range04_duration      +e.speed_range04_duration      )   speed_range04_duration       ,
	 (s.speed_range04_fuel          +e.speed_range04_fuel          )   speed_range04_fuel           ,
	 (s.speed_range05_mileage       +e.speed_range05_mileage       )   speed_range05_mileage        ,
	 (s.speed_range05_duration      +e.speed_range05_duration      )   speed_range05_duration       ,
	 (s.speed_range05_fuel          +e.speed_range05_fuel          )   speed_range05_fuel           ,
	 (s.speed_range06_mileage       +e.speed_range06_mileage       )   speed_range06_mileage        ,
	 (s.speed_range06_duration      +e.speed_range06_duration      )   speed_range06_duration       ,
	 (s.speed_range06_fuel          +e.speed_range06_fuel          )   speed_range06_fuel           ,
	 (s.speed_range07_mileage       +e.speed_range07_mileage       )   speed_range07_mileage        ,
	 (s.speed_range07_duration      +e.speed_range07_duration      )   speed_range07_duration       ,
	 (s.speed_range07_fuel          +e.speed_range07_fuel          )   speed_range07_fuel           ,
	 (s.speed_range08_mileage       +e.speed_range08_mileage       )   speed_range08_mileage        ,
	 (s.speed_range08_duration      +e.speed_range08_duration      )   speed_range08_duration       ,
	 (s.speed_range08_fuel          +e.speed_range08_fuel          )   speed_range08_fuel           ,
	 (s.speed_range09_mileage       +e.speed_range09_mileage       )   speed_range09_mileage        ,
	 (s.speed_range09_duration      +e.speed_range09_duration      )   speed_range09_duration       ,
	 (s.speed_range09_fuel          +e.speed_range09_fuel          )   speed_range09_fuel           ,
	 (s.speed_range10_mileage       +e.speed_range10_mileage       )   speed_range10_mileage        ,
	 (s.speed_range10_duration      +e.speed_range10_duration      )   speed_range10_duration       ,
	 (s.speed_range10_fuel          +e.speed_range10_fuel          )   speed_range10_fuel           ,
	 (s.speed_range11_mileage       +e.speed_range11_mileage       )   speed_range11_mileage        ,
	 (s.speed_range11_duration      +e.speed_range11_duration      )   speed_range11_duration       ,
	 (s.speed_range11_fuel          +e.speed_range11_fuel          )   speed_range11_fuel           ,
	 (s.speed_range12_mileage       +e.speed_range12_mileage       )   speed_range12_mileage        ,
	 (s.speed_range12_duration      +e.speed_range12_duration      )   speed_range12_duration       ,
	 (s.speed_range12_fuel          +e.speed_range12_fuel          )   speed_range12_fuel           ,
	 (s.exceed_speed_range_mileage  +e.exceed_speed_range_mileage  )   exceed_speed_range_mileage   ,
	 (s.exceed_speed_range_duration +e.exceed_speed_range_duration )   exceed_speed_range_duration  ,
	 (s.exceed_speed_range_fuel     +e.exceed_speed_range_fuel     )   exceed_speed_range_fuel      ,
	 (s.assist_brake_mileage        +e.assist_brake_mileage        )   assist_brake_mileage         ,
	 (s.assist_brake_duration       +e.assist_brake_duration       )   assist_brake_duration        ,
	 (s.fatigue_driving_number      +e.fatigue_driving_number      )   fatigue_driving_number       ,
	 (s.park_throttle_duration      +e.park_throttle_duration      )   park_throttle_duration       ,
	 (s.park_throttle_fuel          +e.park_throttle_fuel          )   park_throttle_fuel           ,
	 (s.engine_work_duration        +e.engine_work_duration        )   engine_work_duration         ,
	 (s.driving_handbrake_number    +e.driving_handbrake_number    )   driving_handbrake_number     ,
	 (s.driving_handbrake_duration  +e.driving_handbrake_duration  )   driving_handbrake_duration   ,
	 (s.driving_handbrake_mileage   +e.driving_handbrake_mileage   )   driving_handbrake_mileage    ,
	 (s.idle_handbrake_number       +e.idle_handbrake_number       )   idle_handbrake_number        ,
	 (s.idle_handbrake_duration     +e.idle_handbrake_duration     )   idle_handbrake_duration      ,
	 (s.gear01_mileage              +e.gear01_mileage              )   gear01_mileage               ,
	 (s.gear01_fuel                 +e.gear01_fuel                 )   gear01_fuel                  ,
	 (s.gear01_duration             +e.gear01_duration             )   gear01_duration              ,
	 (s.gear01_number               +e.gear01_number               )   gear01_number                ,
	 (s.gear02_mileage              +e.gear02_mileage              )   gear02_mileage               ,
	 (s.gear02_fuel                 +e.gear02_fuel                 )   gear02_fuel                  ,
	 (s.gear02_duration             +e.gear02_duration             )   gear02_duration              ,
	 (s.gear02_number               +e.gear02_number               )   gear02_number                ,
	 (s.gear03_mileage              +e.gear03_mileage              )   gear03_mileage               ,
	 (s.gear03_fuel                 +e.gear03_fuel                 )   gear03_fuel                  ,
	 (s.gear03_duration             +e.gear03_duration             )   gear03_duration              ,
	 (s.gear03_number               +e.gear03_number               )   gear03_number                ,
	 (s.gear04_mileage              +e.gear04_mileage              )   gear04_mileage               ,
	 (s.gear04_fuel                 +e.gear04_fuel                 )   gear04_fuel                  ,
	 (s.gear04_duration             +e.gear04_duration             )   gear04_duration              ,
	 (s.gear04_number               +e.gear04_number               )   gear04_number                ,
	 (s.gear05_mileage              +e.gear05_mileage              )   gear05_mileage               ,
	 (s.gear05_fuel                 +e.gear05_fuel                 )   gear05_fuel                  ,
	 (s.gear05_duration             +e.gear05_duration             )   gear05_duration              ,
	 (s.gear05_number               +e.gear05_number               )   gear05_number                ,
	 (s.gear06_mileage              +e.gear06_mileage              )   gear06_mileage               ,
	 (s.gear06_fuel                 +e.gear06_fuel                 )   gear06_fuel                  ,
	 (s.gear06_duration             +e.gear06_duration             )   gear06_duration              ,
	 (s.gear06_number               +e.gear06_number               )   gear06_number                ,
	 (s.gear07_mileage              +e.gear07_mileage              )   gear07_mileage               ,
	 (s.gear07_fuel                 +e.gear07_fuel                 )   gear07_fuel                  ,
	 (s.gear07_duration             +e.gear07_duration             )   gear07_duration              ,
	 (s.gear07_number               +e.gear07_number               )   gear07_number                ,
	 (s.gear08_mileage              +e.gear08_mileage              )   gear08_mileage               ,
	 (s.gear08_fuel                 +e.gear08_fuel                 )   gear08_fuel                  ,
	 (s.gear08_duration             +e.gear08_duration             )   gear08_duration              ,
	 (s.gear08_number               +e.gear08_number               )   gear08_number                ,
	 (s.gear09_mileage              +e.gear09_mileage              )   gear09_mileage               ,
	 (s.gear09_fuel                 +e.gear09_fuel                 )   gear09_fuel                  ,
	 (s.gear09_duration             +e.gear09_duration             )   gear09_duration              ,
	 (s.gear09_number               +e.gear09_number               )   gear09_number                ,
	 (s.gear10_mileage              +e.gear10_mileage              )   gear10_mileage               ,
	 (s.gear10_fuel                 +e.gear10_fuel                 )   gear10_fuel                  ,
	 (s.gear10_duration             +e.gear10_duration             )   gear10_duration              ,
	 (s.gear10_number               +e.gear10_number               )   gear10_number                ,
	 (s.gear11_mileage              +e.gear11_mileage              )   gear11_mileage               ,
	 (s.gear11_fuel                 +e.gear11_fuel                 )   gear11_fuel                  ,
	 (s.gear11_duration             +e.gear11_duration             )   gear11_duration              ,
	 (s.gear11_number               +e.gear11_number               )   gear11_number                ,
	 (s.gear12_mileage              +e.gear12_mileage              )   gear12_mileage               ,
	 (s.gear12_fuel                 +e.gear12_fuel                 )   gear12_fuel                  ,
	 (s.gear12_duration             +e.gear12_duration             )   gear12_duration              ,
	 (s.gear12_number               +e.gear12_number               )   gear12_number                ,
	 (s.gear13_mileage              +e.gear13_mileage              )   gear13_mileage               ,
	 (s.gear13_fuel                 +e.gear13_fuel                 )   gear13_fuel                  ,
	 (s.gear13_duration             +e.gear13_duration             )   gear13_duration              ,
	 (s.gear13_number               +e.gear13_number               )   gear13_number                ,
	 (s.gear14_mileage              +e.gear14_mileage              )   gear14_mileage               ,
	 (s.gear14_fuel                 +e.gear14_fuel                 )   gear14_fuel                  ,
	 (s.gear14_duration             +e.gear14_duration             )   gear14_duration              ,
	 (s.gear14_number               +e.gear14_number               )   gear14_number                ,
	 (s.gear15_mileage              +e.gear15_mileage              )   gear15_mileage               ,
	 (s.gear15_fuel                 +e.gear15_fuel                 )   gear15_fuel                  ,
	 (s.gear15_duration             +e.gear15_duration             )   gear15_duration              ,
	 (s.gear15_number               +e.gear15_number               )   gear15_number                ,
	 (s.gear16_mileage              +e.gear16_mileage              )   gear16_mileage               ,
	 (s.gear16_fuel                 +e.gear16_fuel                 )   gear16_fuel                  ,
	 (s.gear16_duration             +e.gear16_duration             )   gear16_duration              ,
	 (s.gear16_number               +e.gear16_number               )   gear16_number                ,
	 (s.gear_other_mileage          +e.gear_other_mileage          )   gear_other_mileage           ,
	 (s.gear_other_fuel             +e.gear_other_fuel             )   gear_other_fuel              ,
	 (s.gear_other_duration         +e.gear_other_duration         )   gear_other_duration          ,
	 (s.gear_other_number           +e.gear_other_number           )   gear_other_number            ,
(s.rpm_range500_mileage       + e.rpm_range500_mileage       )   rpm_range500_mileage         ,
(s.rpm_range500_duration      + e.rpm_range500_duration      )   rpm_range500_duration        ,
(s.rpm_range500_fuel          + e.rpm_range500_fuel          )   rpm_range500_fuel            ,
(s.rpm_range600_mileage       + e.rpm_range600_mileage       )   rpm_range600_mileage         ,
(s.rpm_range600_duration      + e.rpm_range600_duration      )   rpm_range600_duration        ,
(s.rpm_range600_fuel          + e.rpm_range600_fuel          )   rpm_range600_fuel            ,
(s.rpm_range700_mileage       + e.rpm_range700_mileage       )   rpm_range700_mileage         ,
(s.rpm_range700_duration      + e.rpm_range700_duration      )   rpm_range700_duration        ,
(s.rpm_range700_fuel          + e.rpm_range700_fuel          )   rpm_range700_fuel            ,
(s.rpm_range800_mileage       + e.rpm_range800_mileage       )   rpm_range800_mileage         ,
(s.rpm_range800_duration      + e.rpm_range800_duration      )   rpm_range800_duration        ,
(s.rpm_range800_fuel          + e.rpm_range800_fuel          )   rpm_range800_fuel            ,
(s.rpm_range900_mileage       + e.rpm_range900_mileage       )   rpm_range900_mileage         ,
(s.rpm_range900_duration      + e.rpm_range900_duration      )   rpm_range900_duration        ,
(s.rpm_range900_fuel          + e.rpm_range900_fuel          )   rpm_range900_fuel            ,
(s.rpm_range1000_mileage      + e.rpm_range1000_mileage      )   rpm_range1000_mileage        ,
(s.rpm_range1000_duration     + e.rpm_range1000_duration     )   rpm_range1000_duration       ,
(s.rpm_range1000_fuel         + e.rpm_range1000_fuel         )   rpm_range1000_fuel           ,
(s.rpm_range1100_mileage      + e.rpm_range1100_mileage      )   rpm_range1100_mileage        ,
(s.rpm_range1100_duration     + e.rpm_range1100_duration     )   rpm_range1100_duration       ,
(s.rpm_range1100_fuel         + e.rpm_range1100_fuel         )   rpm_range1100_fuel           ,
(s.rpm_range1200_mileage      + e.rpm_range1200_mileage      )   rpm_range1200_mileage        ,
(s.rpm_range1200_duration     + e.rpm_range1200_duration     )   rpm_range1200_duration       ,
(s.rpm_range1200_fuel         + e.rpm_range1200_fuel         )   rpm_range1200_fuel           ,
(s.rpm_range1300_mileage      + e.rpm_range1300_mileage      )   rpm_range1300_mileage        ,
(s.rpm_range1300_duration     + e.rpm_range1300_duration     )   rpm_range1300_duration       ,
(s.rpm_range1300_fuel         + e.rpm_range1300_fuel         )   rpm_range1300_fuel           ,
(s.rpm_range1400_mileage      + e.rpm_range1400_mileage      )   rpm_range1400_mileage        ,
(s.rpm_range1400_duration     + e.rpm_range1400_duration     )   rpm_range1400_duration       ,
(s.rpm_range1400_fuel         + e.rpm_range1400_fuel         )   rpm_range1400_fuel           ,
(s.rpm_range1500_mileage      + e.rpm_range1500_mileage      )   rpm_range1500_mileage        ,
(s.rpm_range1500_duration     + e.rpm_range1500_duration     )   rpm_range1500_duration       ,
(s.rpm_range1500_fuel         + e.rpm_range1500_fuel         )   rpm_range1500_fuel           ,
(s.rpm_range1600_mileage      + e.rpm_range1600_mileage      )   rpm_range1600_mileage        ,
(s.rpm_range1600_duration     + e.rpm_range1600_duration     )   rpm_range1600_duration       ,
(s.rpm_range1600_fuel         + e.rpm_range1600_fuel         )   rpm_range1600_fuel           ,
(s.rpm_range1700_mileage      + e.rpm_range1700_mileage      )   rpm_range1700_mileage        ,
(s.rpm_range1700_duration     + e.rpm_range1700_duration     )   rpm_range1700_duration       ,
(s.rpm_range1700_fuel         + e.rpm_range1700_fuel         )   rpm_range1700_fuel           ,
(s.rpm_range1800_mileage      + e.rpm_range1800_mileage      )   rpm_range1800_mileage        ,
(s.rpm_range1800_duration     + e.rpm_range1800_duration     )   rpm_range1800_duration       ,
(s.rpm_range1800_fuel         + e.rpm_range1800_fuel         )   rpm_range1800_fuel           ,
(s.rpm_range1900_mileage      + e.rpm_range1900_mileage      )   rpm_range1900_mileage        ,
(s.rpm_range1900_duration     + e.rpm_range1900_duration     )   rpm_range1900_duration       ,
(s.rpm_range1900_fuel         + e.rpm_range1900_fuel         )   rpm_range1900_fuel           ,
(s.rpm_range2000_mileage      + e.rpm_range2000_mileage      )   rpm_range2000_mileage        ,
(s.rpm_range2000_duration     + e.rpm_range2000_duration     )   rpm_range2000_duration       ,
(s.rpm_range2000_fuel         + e.rpm_range2000_fuel         )   rpm_range2000_fuel           ,
(s.rpm_range2100_mileage      + e.rpm_range2100_mileage      )   rpm_range2100_mileage        ,
(s.rpm_range2100_duration     + e.rpm_range2100_duration     )   rpm_range2100_duration       ,
(s.rpm_range2100_fuel         + e.rpm_range2100_fuel         )   rpm_range2100_fuel           ,
(s.rpm_range2200_mileage      + e.rpm_range2200_mileage      )   rpm_range2200_mileage        ,
(s.rpm_range2200_duration     + e.rpm_range2200_duration     )   rpm_range2200_duration       ,
(s.rpm_range2200_fuel         + e.rpm_range2200_fuel         )   rpm_range2200_fuel           ,
(s.rpm_range2300_mileage      + e.rpm_range2300_mileage      )   rpm_range2300_mileage        ,
(s.rpm_range2300_duration     + e.rpm_range2300_duration     )   rpm_range2300_duration       ,
(s.rpm_range2300_fuel         + e.rpm_range2300_fuel         )   rpm_range2300_fuel           ,
(s.rpm_range2400_mileage      + e.rpm_range2400_mileage      )   rpm_range2400_mileage        ,
(s.rpm_range2400_duration     + e.rpm_range2400_duration     )   rpm_range2400_duration       ,
(s.rpm_range2400_fuel         + e.rpm_range2400_fuel         )   rpm_range2400_fuel           ,
(s.rpm_range2500_mileage      + e.rpm_range2500_mileage      )   rpm_range2500_mileage        ,
(s.rpm_range2500_duration     + e.rpm_range2500_duration     )   rpm_range2500_duration       ,
(s.rpm_range2500_fuel         + e.rpm_range2500_fuel         )   rpm_range2500_fuel           ,
(s.rpm_range2600_mileage      + e.rpm_range2600_mileage      )   rpm_range2600_mileage        ,
(s.rpm_range2600_duration     + e.rpm_range2600_duration     )   rpm_range2600_duration       ,
(s.rpm_range2600_fuel         + e.rpm_range2600_fuel         )   rpm_range2600_fuel           ,
(s.rpm_range2700_mileage      + e.rpm_range2700_mileage      )   rpm_range2700_mileage        ,
(s.rpm_range2700_duration     + e.rpm_range2700_duration     )   rpm_range2700_duration       ,
(s.rpm_range2700_fuel         + e.rpm_range2700_fuel         )   rpm_range2700_fuel           ,
(s.rpm_range2800_mileage      + e.rpm_range2800_mileage      )   rpm_range2800_mileage        ,
(s.rpm_range2800_duration     + e.rpm_range2800_duration     )   rpm_range2800_duration       ,
(s.rpm_range2800_fuel         + e.rpm_range2800_fuel         )   rpm_range2800_fuel           ,
(s.rpm_range2900_mileage      + e.rpm_range2900_mileage      )   rpm_range2900_mileage        ,
(s.rpm_range2900_duration     + e.rpm_range2900_duration     )   rpm_range2900_duration       ,
(s.rpm_range2900_fuel         + e.rpm_range2900_fuel         )   rpm_range2900_fuel           ,
(s.rpm_range3000_mileage      + e.rpm_range3000_mileage      )   rpm_range3000_mileage        ,
(s.rpm_range3000_duration     + e.rpm_range3000_duration     )   rpm_range3000_duration       ,
(s.rpm_range3000_fuel         + e.rpm_range3000_fuel         )   rpm_range3000_fuel           ,
(s.rpm_range_over3000_mileage + e.rpm_range_over3000_mileage )   rpm_range_over3000_mileage   ,
(s.rpm_range_over3000_duration+ e.rpm_range_over3000_duration)   rpm_range_over3000_duration  ,
(s.rpm_range_over3000_fuel    + e.rpm_range_over3000_fuel    )   rpm_range_over3000_fuel      ,
	  concat(s.altitude_ranges,',',e.altitude_ranges)    altitude_ranges,
	  concat(s.clock_ranges,',',e.clock_ranges)         clock_ranges   ,
	 (s.work_water_temp50_duration        +e.work_water_temp50_duration      )   work_water_temp50_duration       ,
	 (s.work_water_temp70_duration        +e.work_water_temp70_duration      )   work_water_temp70_duration       ,
	 (s.work_water_temp80_duration        +e.work_water_temp80_duration      )   work_water_temp80_duration       ,
	 (s.work_water_temp85_duration        +e.work_water_temp85_duration      )   work_water_temp85_duration       ,
	 (s.work_water_temp90_duration        +e.work_water_temp90_duration      )   work_water_temp90_duration       ,
	 (s.work_water_temp95_duration        +e.work_water_temp95_duration      )   work_water_temp95_duration       ,
	 (s.work_water_temp100_duration       +e.work_water_temp100_duration     )   work_water_temp100_duration      ,
	 (s.work_water_temp105_duration       +e.work_water_temp105_duration     )   work_water_temp105_duration      ,
	 (s.work_water_temp110_duration       +e.work_water_temp110_duration     )   work_water_temp110_duration      ,
	 (s.work_water_temp115_duration       +e.work_water_temp115_duration     )   work_water_temp115_duration      ,
	 (s.work_water_temp120_duration       +e.work_water_temp120_duration     )   work_water_temp120_duration      ,
	 (s.work_water_temp125_duration       +e.work_water_temp125_duration     )   work_water_temp125_duration      ,
	 (s.work_water_temp130_duration       +e.work_water_temp130_duration     )   work_water_temp130_duration      ,
	 (s.work_water_temp135_duration       +e.work_water_temp135_duration     )   work_water_temp135_duration      ,
	 (s.work_water_temp140_duration       +e.work_water_temp140_duration     )   work_water_temp140_duration      ,
	 (s.work_water_temp145_duration       +e.work_water_temp145_duration     )   work_water_temp145_duration      ,
	 (s.work_water_temp150_duration       +e.work_water_temp150_duration     )   work_water_temp150_duration      ,
	 (s.work_water_temp_over150_duration  +e.work_water_temp_over150_duration)   work_water_temp_over150_duration ,
	 (s.idle_water_temp50_duration        +e.idle_water_temp50_duration      )   idle_water_temp50_duration       ,
	 (s.idle_water_temp70_duration        +e.idle_water_temp70_duration      )   idle_water_temp70_duration       ,
	 (s.idle_water_temp80_duration        +e.idle_water_temp80_duration      )   idle_water_temp80_duration       ,
	 (s.idle_water_temp85_duration        +e.idle_water_temp85_duration      )   idle_water_temp85_duration       ,
	 (s.idle_water_temp90_duration        +e.idle_water_temp90_duration      )   idle_water_temp90_duration       ,
	 (s.idle_water_temp95_duration        +e.idle_water_temp95_duration      )   idle_water_temp95_duration       ,
	 (s.idle_water_temp100_duration       +e.idle_water_temp100_duration     )   idle_water_temp100_duration      ,
	 (s.idle_water_temp105_duration       +e.idle_water_temp105_duration     )   idle_water_temp105_duration      ,
	 (s.idle_water_temp110_duration       +e.idle_water_temp110_duration     )   idle_water_temp110_duration      ,
	 (s.idle_water_temp115_duration       +e.idle_water_temp115_duration     )   idle_water_temp115_duration      ,
	 (s.idle_water_temp120_duration       +e.idle_water_temp120_duration     )   idle_water_temp120_duration      ,
	 (s.idle_water_temp125_duration       +e.idle_water_temp125_duration     )   idle_water_temp125_duration      ,
	 (s.idle_water_temp130_duration       +e.idle_water_temp130_duration     )   idle_water_temp130_duration      ,
	 (s.idle_water_temp135_duration       +e.idle_water_temp135_duration     )   idle_water_temp135_duration      ,
	 (s.idle_water_temp140_duration       +e.idle_water_temp140_duration     )   idle_water_temp140_duration      ,
	 (s.idle_water_temp145_duration       +e.idle_water_temp145_duration     )   idle_water_temp145_duration      ,
	 (s.idle_water_temp150_duration       +e.idle_water_temp150_duration     )   idle_water_temp150_duration      ,
	 (s.idle_water_temp_over150_duration  +e.idle_water_temp_over150_duration)   idle_water_temp_over150_duration ,
	 (case when s.max_work_water_temp >= e.max_work_water_temp  then s.max_work_water_temp else e.max_work_water_temp end)   max_work_water_temp,
	 (case when s.min_work_water_temp >= e.min_work_water_temp  then e.min_work_water_temp else s.min_work_water_temp end)   min_work_water_temp,
	 (s.work_water_temp_sum  + e.work_water_temp_sum)   work_water_temp_sum,
	 (s.work_water_temp_count + e.work_water_temp_count)   work_water_temp_count,
	 (case when s.max_idle_water_temp >= e.max_idle_water_temp  then s.max_idle_water_temp else e.max_idle_water_temp end)   max_idle_water_temp,
	 (case when s.min_idle_water_temp >= e.min_idle_water_temp  then e.min_idle_water_temp else s.min_idle_water_temp end)   min_idle_water_temp,
	 (s.idle_water_temp_sum + e.idle_water_temp_sum)   idle_water_temp_sum,
	 (s.idle_water_temp_count + e.idle_water_temp_count)   idle_water_temp_count,
	 (s.gas_open_percent0_duration   + e.gas_open_percent0_duration  )    gas_open_percent0_duration   ,
	 (s.gas_open_percent10_duration  + e.gas_open_percent10_duration )    gas_open_percent10_duration  ,
	 (s.gas_open_percent20_duration  + e.gas_open_percent20_duration )    gas_open_percent20_duration  ,
	 (s.gas_open_percent30_duration  + e.gas_open_percent30_duration )    gas_open_percent30_duration  ,
	 (s.gas_open_percent40_duration  + e.gas_open_percent40_duration )    gas_open_percent40_duration  ,
	 (s.gas_open_percent50_duration  + e.gas_open_percent50_duration )    gas_open_percent50_duration  ,
	 (s.gas_open_percent60_duration  + e.gas_open_percent60_duration )    gas_open_percent60_duration  ,
	 (s.gas_open_percent70_duration  + e.gas_open_percent70_duration )    gas_open_percent70_duration  ,
	 (s.gas_open_percent80_duration  + e.gas_open_percent80_duration )    gas_open_percent80_duration  ,
	 (s.gas_open_percent90_duration  + e.gas_open_percent90_duration )    gas_open_percent90_duration  ,
	 (s.gas_open_percent99_duration  + e.gas_open_percent99_duration )    gas_open_percent99_duration  ,
	 (s.gas_open_percent100_duration + e.gas_open_percent100_duration)    gas_open_percent100_duration ,
	 (s.torque0_duration             + e.torque0_duration            )    torque0_duration             ,
	 (s.torque10_duration            + e.torque10_duration           )    torque10_duration            ,
	 (s.torque20_duration            + e.torque20_duration           )    torque20_duration            ,
	 (s.torque30_duration            + e.torque30_duration           )    torque30_duration            ,
	 (s.torque40_duration            + e.torque40_duration           )    torque40_duration            ,
	 (s.torque50_duration            + e.torque50_duration           )    torque50_duration            ,
	 (s.torque60_duration            + e.torque60_duration           )    torque60_duration            ,
	 (s.torque70_duration            + e.torque70_duration           )    torque70_duration            ,
	 (s.torque80_duration            + e.torque80_duration           )    torque80_duration            ,
	 (s.torque90_duration            + e.torque90_duration           )    torque90_duration            ,
	 (s.torque99_duration            + e.torque99_duration           )    torque99_duration            ,
	 (s.torque100_duration           + e.torque100_duration          )    torque100_duration           ,
	 (case when s.max_torque >= e.max_torque  then s.max_torque else e.max_torque end)   max_torque,
	 (case when s.min_torque >= e.min_torque  then e.min_torque else s.min_torque end)   min_torque,
	 (s.torque_sum         +e.torque_sum        )   torque_sum               ,
	 (s.torque_count       +e.torque_count      )   torque_count             ,
	 (s.no_idle_rpm_sum    +e.no_idle_rpm_sum   )   no_idle_rpm_sum          ,
	 (s.no_idle_rpm_count  +e.no_idle_rpm_count )   no_idle_rpm_count        ,
	 (s.rpm_sum            +e.rpm_sum           )   rpm_sum                  ,
	 (s.rpm_count          +e.rpm_count         )   rpm_count                ,
	 (case when s.max_rpm >= e.max_rpm  then s.max_rpm else e.max_rpm end)   max_rpm,
	 (case when s.min_rpm >= e.min_rpm  then e.min_rpm else s.min_rpm end)   min_rpm,
	 (s.iat50_duration        +e.iat50_duration       )   iat50_duration      ,
	 (s.iat70_duration        +e.iat70_duration       )   iat70_duration      ,
	 (s.iat80_duration        +e.iat80_duration       )   iat80_duration      ,
	 (s.iat85_duration        +e.iat85_duration       )   iat85_duration      ,
	 (s.iat90_duration        +e.iat90_duration       )   iat90_duration      ,
	 (s.iat95_duration        +e.iat95_duration       )   iat95_duration      ,
	 (s.iat100_duration       +e.iat100_duration      )   iat100_duration     ,
	 (s.iat105_duration       +e.iat105_duration      )   iat105_duration     ,
	 (s.iat110_duration       +e.iat110_duration      )   iat110_duration     ,
	 (s.iat115_duration       +e.iat115_duration      )   iat115_duration     ,
	 (s.iat120_duration       +e.iat120_duration      )   iat120_duration     ,
	 (s.iat125_duration       +e.iat125_duration      )   iat125_duration     ,
	 (s.iat130_duration       +e.iat130_duration      )   iat130_duration     ,
	 (s.iat135_duration       +e.iat135_duration      )   iat135_duration     ,
	 (s.iat140_duration       +e.iat140_duration      )   iat140_duration     ,
	 (s.iat145_duration       +e.iat145_duration      )   iat145_duration     ,
	 (s.iat150_duration       +e.iat150_duration      )   iat150_duration     ,
	 (s.iat_over150_duration  +e.iat_over150_duration )   iat_over150_duration,
	 (s.iat_sum               +e.iat_sum              )   iat_sum             ,
	 (s.iat_count             +e.iat_count            )   iat_count           ,
	 (case when s.max_iat >= e.max_iat  then s.max_iat else e.max_iat end)   max_iat,
	 (case when s.min_iat >= e.min_iat  then e.min_iat else s.min_iat end)   min_iat,
	 (s.ip50_duration       + e.ip50_duration       )   ip50_duration        ,
	 (s.ip60_duration       + e.ip60_duration       )   ip60_duration        ,
	 (s.ip70_duration       + e.ip70_duration       )   ip70_duration        ,
	 (s.ip80_duration       + e.ip80_duration       )   ip80_duration        ,
	 (s.ip90_duration       + e.ip90_duration       )   ip90_duration        ,
	 (s.ip100_duration      + e.ip100_duration      )   ip100_duration       ,
	 (s.ip110_duration      + e.ip110_duration      )   ip110_duration       ,
	 (s.ip120_duration      + e.ip120_duration      )   ip120_duration       ,
	 (s.ip130_duration      + e.ip130_duration      )   ip130_duration       ,
	 (s.ip140_duration      + e.ip140_duration      )   ip140_duration       ,
	 (s.ip150_duration      + e.ip150_duration      )   ip150_duration       ,
	 (s.ip_over150_duration + e.ip_over150_duration )   ip_over150_duration  ,
	 (s.ip_sum              + e.ip_sum              )   ip_sum               ,
	 (s.ip_count            + e.ip_count            )   ip_count             ,
	 (case when s.max_ip >= e.max_ip  then s.max_ip else e.max_ip end)   max_ip,
	 (case when s.min_ip >= e.min_ip  then e.min_ip else s.min_ip end)   min_ip,
	 concat(s.cross_cities,',',e.cross_cities)  cross_cities,
	(s.so90_number                +e.so90_number             )   so90_number                ,
	(s.so90_duration              +e.so90_duration           )   so90_duration              ,
	(s.so90_mileage               +e.so90_mileage            )   so90_mileage               ,
	(s.so120_number               +e.so120_number            )   so120_number               ,
	(s.so120_duration             +e.so120_duration          )   so120_duration             ,
	(s.so120_mileage              +e.so120_mileage           )   so120_mileage              ,
	(s.sr_number                  +e.sr_number               )   sr_number                  ,
	(s.sr_duration                +e.sr_duration             )   sr_duration                ,
	(s.sr_mileage                 +e.sr_mileage              )   sr_mileage                 ,
	(s.sr_fuel                    +e.sr_fuel                 )   sr_fuel                    ,
	(s.lrog_number                +e.lrog_number             )   lrog_number                ,
	(s.lrog_duration              +e.lrog_duration           )   lrog_duration              ,
	(s.lrog_mileage               +e.lrog_mileage            )   lrog_mileage               ,
	(s.lrog_fuel                  +e.lrog_fuel               )   lrog_fuel                  ,
	(s.hsb_number                 +e.hsb_number              )   hsb_number                 ,
	(s.hsb_duration               +e.hsb_duration            )   hsb_duration               ,
	(s.hsb_mileage                +e.hsb_mileage             )   hsb_mileage                ,
	(s.csb_number                 +e.csb_number              )   csb_number                 ,
	(s.csb_duration               +e.csb_duration            )   csb_duration               ,
	(s.csb_mileage                +e.csb_mileage             )   csb_mileage                ,
	(s.clutch_number              +e.clutch_number           )   clutch_number              ,
	(s.clutch_duration            +e.clutch_duration         )   clutch_duration            ,
	(s.clutch_mileage             +e.clutch_mileage          )   clutch_mileage             ,
	(s.sharp_turning_number       +e.sharp_turning_number    )   sharp_turning_number       ,
	(s.sharp_turning_mil          +e.sharp_turning_mil       )   sharp_turning_mil          ,
	(s.so90_fuel                  +e.so90_fuel               )   so90_fuel                  ,
	(s.so120_fuel                 +e.so120_fuel              )   so120_fuel                 ,
	(s.fatigue_driving_duration   +e.fatigue_driving_duration)   fatigue_driving_duration   ,
	 s.precise_flag   precise_flag,
	 s.wload_type    wload_type,
	 concat(s.cross_cities_detail,',',e.cross_cities_detail)   cross_cities_detail,
	 round((s.trip_fuel + e.trip_fuel)/(s.trip_mileage + e.trip_mileage),2) *100   fuel_100km,
	 round((s.trip_mileage + e.trip_mileage)/(s.trip_duration+e.trip_duration-s.idle_duration-e.trip_duration)*3.6,0)    avg_speed_noidle,
	 round((s.trip_mileage + e.trip_mileage)/(s.trip_duration+e.trip_duration)*3.6,0)    avg_speed,
	 round((s.eco_driving_duration+e.eco_driving_duration)/(s.trip_duration+e.trip_duration),2)*100   eco_proportion,
	 s.hsb_number+s.csb_number+e.hsb_number+e.csb_number   brake_times,
	 (s.sharp_up_speed1_mileage    +e.sharp_up_speed1_mileage     )   sharp_up_speed1_mileage      ,
	 (s.sharp_up_speed1_fuel       +e.sharp_up_speed1_fuel        )   sharp_up_speed1_fuel         ,
	 (s.sharp_up_speed1_duration   +e.sharp_up_speed1_duration    )   sharp_up_speed1_duration     ,
	 (s.sharp_up_speed1_number     +e.sharp_up_speed1_number      )   sharp_up_speed1_number       ,
	 (s.sharp_down_speed1_mileage  +e.sharp_down_speed1_mileage   )   sharp_down_speed1_mileage    ,
	 (s.sharp_down_speed1_fuel     +e.sharp_down_speed1_fuel   )      sharp_down_speed1_fuel    ,
	 (s.sharp_down_speed1_duration +e.sharp_down_speed1_duration  )   sharp_down_speed1_duration   ,
	 (s.sharp_down_speed1_number   +e.sharp_down_speed1_number    )   sharp_down_speed1_number     ,
	 (s.sharp_turning1_number      +e.sharp_turning1_number       )   sharp_turning1_number ,
     (s.long_idle_fuel            +e.long_idle_fuel         )          long_idle_fuel         ,
      (s.long_idle_duration        +e.long_idle_duration     )          long_idle_duration     ,
      (s.long_idle_number          +e.long_idle_number       )          long_idle_number       ,
      (s.cold_running_mileage     +e.cold_running_mileage  )            cold_running_mileage  ,
      (s.cold_running_fuel       +e.cold_running_fuel    )              cold_running_fuel    ,
      (s.cold_running_duration     +e.cold_running_duration  )          cold_running_duration  ,
      (s.cold_running_number       +e.cold_running_number    )          cold_running_number    ,
      (s.night_running_mileage     +e.night_running_mileage  )          night_running_mileage  ,
      (s.night_running_fuel        +e.night_running_fuel     )          night_running_fuel     ,
      (s.night_running_duration    +e.night_running_duration )          night_running_duration ,
      (s.night_running_number      +e.night_running_number   )          night_running_number   ,
      (s.high_rotation_mileage     +e.high_rotation_mileage  )          high_rotation_mileage  ,
      (s.high_rotation_fuel        +e.high_rotation_fuel     )          high_rotation_fuel     ,
      (s.high_rotation_duration    +e.high_rotation_duration )          high_rotation_duration ,
      (s.high_rotation_number      +e.high_rotation_number   )          high_rotation_number   ,
      (s.large_gas_mileage         +e.large_gas_mileage      )          large_gas_mileage      ,
      (s.large_gas_fuel            +e.large_gas_fuel         )          large_gas_fuel         ,
      (s.large_gas_duration        +e.large_gas_duration     )          large_gas_duration     ,
      (s.large_gas_number          +e.large_gas_number       )          large_gas_number       ,
      (s.full_gas_mileage          +e.full_gas_mileage       )          full_gas_mileage       ,
      (s.full_gas_fuel             +e.full_gas_fuel          )          full_gas_fuel          ,
      (s.full_gas_duration         +e.full_gas_duration      )          full_gas_duration      ,
      (s.full_gas_number           +e.full_gas_number        )          full_gas_number        ,
      (s.fast_speed_mileage        +e.fast_speed_mileage         )          fast_speed_mileage         ,
      (s.fast_speed_fuel           +e.fast_speed_fuel            )          fast_speed_fuel            ,
      (s.fast_speed_duration       +e.fast_speed_duration        )          fast_speed_duration        ,
      (s.fast_speed_number         +e.fast_speed_number          )          fast_speed_number          ,
	    (s.neutral_gear_coast1_mileage  +e.neutral_gear_coast1_mileage  )  neutral_gear_coast1_mileage ,
		(s.neutral_gear_coast1_fuel     +e.neutral_gear_coast1_fuel     )  neutral_gear_coast1_fuel    ,
		(s.neutral_gear_coast1_duration +e.neutral_gear_coast1_duration )  neutral_gear_coast1_duration,
		(s.neutral_gear_coast1_number   +e.neutral_gear_coast1_number   )  neutral_gear_coast1_number  ,
		(s.flameout_coast_mileage       +e.flameout_coast_mileage       )  flameout_coast_mileage      ,
		(s.flameout_coast_fuel          +e.flameout_coast_fuel          )  flameout_coast_fuel         ,
		(s.flameout_coast_duration      +e.flameout_coast_duration      )  flameout_coast_duration     ,
		(s.flameout_coast_number        +e.flameout_coast_number        )  flameout_coast_number       ,
		s.amt_load          amt_load            ,
		s.vecu_load         vecu_load            ,
		s.load_type         load_type             ,
		s.trip_mileage                    trip_mileage_pre,
        s.trip_fuel                       trip_fuel_pre,
        s.trip_duration                   trip_duration_pre,
      (s.totalScore     )          totalScore             ,
      (s.safeScore                             )          safeScore              ,
      (s.ecoScore                                )          ecoScore               ,

       '${day}'  day

from
 (select * from ${hive_db}.trip_statistics where day = '${day_ago}' and cross_day_flag= 2)  s
   join
 (select * from ${hive_db}.trip_statistics where day = '${day}' and cross_day_flag= 1 ) e
 on s.tid = e.tid
union all
select uid,tid,trip_id,route_start_time,route_start_lc,route_start_latitude,route_start_longitude,route_start_height,route_start_direction,route_start_mileage,route_start_fuel,route_end_time,route_end_lc,route_end_latitude,route_end_longitude,route_end_height,route_end_direction,route_end_mileage,route_end_fuel,route_integral_mileage,route_integral_fuel,trip_power,stype,trip_mileage,trip_fuel,trip_duration,route_stop_flag,cross_day_flag,force_end_flag,max_speed,min_speed,max_useful_speed,min_useful_speed,in_gear_coast_mileage,in_gear_coast_duration,in_gear_coast_number,brake_number,brake_mileage,brake_duration,vehicle_estimated_load,neutral_gear_coast_duration,neutral_gear_coast_fuel,neutral_gear_coast_mileage,neutral_gear_coast_number,idle_fuel,idle_duration,idle_number,parking_power_on_duration,city_driving_mileage,city_driving_duration,city_driving_fuel,rural_driving_mileage,rural_driving_duration,rural_driving_fuel,eco_driving_mileage,eco_driving_duration,eco_driving_fuel,up_slope_driving_mileage,up_slope_driving_duration,up_slope_driving_fuel,down_slope_driving_mileage,down_slope_driving_duration,down_slope_driving_fuel,level_road_driving_mileage,level_road_driving_duration,level_road_driving_fuel,cruise_mileage,cruise_duration,cruise_fuel,shift_gears_number,sharp_down_speed_duration,sharp_down_speed_mileage,sharp_down_speed_number,sharp_up_speed_duration,sharp_up_speed_mileage,sharp_up_speed_number,sharp_up_speed_fuel,sharp_step_on_gas_mileage,sharp_step_on_gas_duration,sharp_step_on_gas_fuel,sharp_step_on_gas_number,vehicle_park_number,vehicle_start_number,vehicle_start_mileage,vehicle_start_duration,vehicle_start_fuel,vehicle_cold_start_number,vehicle_cold_start_mileage,vehicle_cold_start_duration,vehicle_cold_start_fuel,charge_fuel_percent,charge_fuel,leak_fuel_percent,leak_fuel,gas_stability_number,gas_stability_duration,gas_open_percent_sum,gas_open_percent_count,speed_range01_mileage,speed_range01_duration,speed_range01_fuel,speed_range02_mileage,speed_range02_duration,speed_range02_fuel,speed_range03_mileage,speed_range03_duration,speed_range03_fuel,speed_range04_mileage,speed_range04_duration,speed_range04_fuel,speed_range05_mileage,speed_range05_duration,speed_range05_fuel,speed_range06_mileage,speed_range06_duration,speed_range06_fuel,speed_range07_mileage,speed_range07_duration,speed_range07_fuel,speed_range08_mileage,speed_range08_duration,speed_range08_fuel,speed_range09_mileage,speed_range09_duration,speed_range09_fuel,speed_range10_mileage,speed_range10_duration,speed_range10_fuel,speed_range11_mileage,speed_range11_duration,speed_range11_fuel,speed_range12_mileage,speed_range12_duration,speed_range12_fuel,exceed_speed_range_mileage,exceed_speed_range_duration,exceed_speed_range_fuel,assist_brake_mileage,assist_brake_duration,fatigue_driving_number,park_throttle_duration,park_throttle_fuel,engine_work_duration,driving_handbrake_number,driving_handbrake_duration,driving_handbrake_mileage,idle_handbrake_number,idle_handbrake_duration,gear01_mileage,gear01_fuel,gear01_duration,gear01_number,gear02_mileage,gear02_fuel,gear02_duration,gear02_number,gear03_mileage,gear03_fuel,gear03_duration,gear03_number,gear04_mileage,gear04_fuel,gear04_duration,gear04_number,gear05_mileage,gear05_fuel,gear05_duration,gear05_number,gear06_mileage,gear06_fuel,gear06_duration,gear06_number,gear07_mileage,gear07_fuel,gear07_duration,gear07_number,gear08_mileage,gear08_fuel,gear08_duration,gear08_number,gear09_mileage,gear09_fuel,gear09_duration,gear09_number,gear10_mileage,gear10_fuel,gear10_duration,gear10_number,gear11_mileage,gear11_fuel,gear11_duration,gear11_number,gear12_mileage,gear12_fuel,gear12_duration,gear12_number,gear13_mileage,gear13_fuel,gear13_duration,gear13_number,gear14_mileage,gear14_fuel,gear14_duration,gear14_number,gear15_mileage,gear15_fuel,gear15_duration,gear15_number,gear16_mileage,gear16_fuel,gear16_duration,gear16_number,gear_other_mileage,gear_other_fuel,gear_other_duration,gear_other_number,rpm_range500_mileage,rpm_range500_duration,rpm_range500_fuel,rpm_range600_mileage,rpm_range600_duration,rpm_range600_fuel,rpm_range700_mileage,rpm_range700_duration,rpm_range700_fuel,rpm_range800_mileage,rpm_range800_duration,rpm_range800_fuel,rpm_range900_mileage,rpm_range900_duration,rpm_range900_fuel,rpm_range1000_mileage,rpm_range1000_duration,rpm_range1000_fuel,rpm_range1100_mileage,rpm_range1100_duration,rpm_range1100_fuel,rpm_range1200_mileage,rpm_range1200_duration,rpm_range1200_fuel,rpm_range1300_mileage,rpm_range1300_duration,rpm_range1300_fuel,rpm_range1400_mileage,rpm_range1400_duration,rpm_range1400_fuel,rpm_range1500_mileage,rpm_range1500_duration,rpm_range1500_fuel,rpm_range1600_mileage,rpm_range1600_duration,rpm_range1600_fuel,rpm_range1700_mileage,rpm_range1700_duration,rpm_range1700_fuel,rpm_range1800_mileage,rpm_range1800_duration,rpm_range1800_fuel,rpm_range1900_mileage,rpm_range1900_duration,rpm_range1900_fuel,rpm_range2000_mileage,rpm_range2000_duration,rpm_range2000_fuel,rpm_range2100_mileage,rpm_range2100_duration,rpm_range2100_fuel,rpm_range2200_mileage,rpm_range2200_duration,rpm_range2200_fuel,rpm_range2300_mileage,rpm_range2300_duration,rpm_range2300_fuel,rpm_range2400_mileage,rpm_range2400_duration,rpm_range2400_fuel,rpm_range2500_mileage,rpm_range2500_duration,rpm_range2500_fuel,rpm_range2600_mileage,rpm_range2600_duration,rpm_range2600_fuel,rpm_range2700_mileage,rpm_range2700_duration,rpm_range2700_fuel,rpm_range2800_mileage,rpm_range2800_duration,rpm_range2800_fuel,rpm_range2900_mileage,rpm_range2900_duration,rpm_range2900_fuel,rpm_range3000_mileage,rpm_range3000_duration,rpm_range3000_fuel,rpm_range_over3000_mileage,rpm_range_over3000_duration,rpm_range_over3000_fuel,altitude_ranges,clock_ranges,work_water_temp50_duration,work_water_temp70_duration,work_water_temp80_duration,work_water_temp85_duration,work_water_temp90_duration,work_water_temp95_duration,work_water_temp100_duration,work_water_temp105_duration,work_water_temp110_duration,work_water_temp115_duration,work_water_temp120_duration,work_water_temp125_duration,work_water_temp130_duration,work_water_temp135_duration,work_water_temp140_duration,work_water_temp145_duration,work_water_temp150_duration,work_water_temp_over150_duration,idle_water_temp50_duration,idle_water_temp70_duration,idle_water_temp80_duration,idle_water_temp85_duration,idle_water_temp90_duration,idle_water_temp95_duration,idle_water_temp100_duration,idle_water_temp105_duration,idle_water_temp110_duration,idle_water_temp115_duration,idle_water_temp120_duration,idle_water_temp125_duration,idle_water_temp130_duration,idle_water_temp135_duration,idle_water_temp140_duration,idle_water_temp145_duration,idle_water_temp150_duration,idle_water_temp_over150_duration,max_work_water_temp,min_work_water_temp,work_water_temp_sum,work_water_temp_count,max_idle_water_temp,min_idle_water_temp,idle_water_temp_sum,idle_water_temp_count,gas_open_percent0_duration,gas_open_percent10_duration,gas_open_percent20_duration,gas_open_percent30_duration,gas_open_percent40_duration,gas_open_percent50_duration,gas_open_percent60_duration,gas_open_percent70_duration,gas_open_percent80_duration,gas_open_percent90_duration,gas_open_percent99_duration,gas_open_percent100_duration,torque0_duration,torque10_duration,torque20_duration,torque30_duration,torque40_duration,torque50_duration,torque60_duration,torque70_duration,torque80_duration,torque90_duration,torque99_duration,torque100_duration,max_torque,min_torque,torque_sum,torque_count,no_idle_rpm_sum,no_idle_rpm_count,rpm_sum,rpm_count,max_rpm,min_rpm,iat50_duration,iat70_duration,iat80_duration,iat85_duration,iat90_duration,iat95_duration,iat100_duration,iat105_duration,iat110_duration,iat115_duration,iat120_duration,iat125_duration,iat130_duration,iat135_duration,iat140_duration,iat145_duration,iat150_duration,iat_over150_duration,iat_sum,iat_count,max_iat,min_iat,ip50_duration,ip60_duration,ip70_duration,ip80_duration,ip90_duration,ip100_duration,ip110_duration,ip120_duration,ip130_duration,ip140_duration,ip150_duration,ip_over150_duration,ip_sum,ip_count,max_ip,min_ip,cross_cities,so90_number,so90_duration,so90_mileage,so120_number,so120_duration,so120_mileage,sr_number,sr_duration,sr_mileage,sr_fuel,lrog_number,lrog_duration,lrog_mileage,lrog_fuel,hsb_number,hsb_duration,hsb_mileage,csb_number,csb_duration,csb_mileage,clutch_number,clutch_duration,clutch_mileage,sharp_turning_number,sharp_turning_mil,so90_fuel,so120_fuel,fatigue_driving_duration,precise_flag,wload_type,cross_cities_detail,fuel_100km,avg_speed_noidle,avg_speed,eco_proportion,brake_times,sharp_up_speed1_mileage,sharp_up_speed1_fuel,sharp_up_speed1_duration,sharp_up_speed1_number,sharp_down_speed1_mileage,sharp_down_speed1_fuel,sharp_down_speed1_duration,sharp_down_speed1_number,sharp_turning1_number,long_idle_fuel,long_idle_duration,long_idle_number,cold_running_mileage,cold_running_fuel,cold_running_duration,cold_running_number,night_running_mileage,night_running_fuel,night_running_duration,night_running_number,high_rotation_mileage,high_rotation_fuel,high_rotation_duration,high_rotation_number,large_gas_mileage,large_gas_fuel,large_gas_duration,large_gas_number,full_gas_mileage,full_gas_fuel,full_gas_duration,full_gas_number,fast_speed_mileage,fast_speed_fuel,fast_speed_duration,fast_speed_number,neutral_gear_coast1_mileage,neutral_gear_coast1_fuel,neutral_gear_coast1_duration,neutral_gear_coast1_number,flameout_coast_mileage,flameout_coast_fuel,flameout_coast_duration,flameout_coast_number,amt_load,vecu_load,load_type,trip_mileage trip_mileage_pre,trip_fuel trip_fuel_pre,trip_duration trip_duration_pre,totalScore,safeScore,ecoScore,day from ${hive_db}.trip_statistics d where day = '${day}' and cross_day_flag= 0 ;
EOF
)

CUR_DIR=$(cd "$(dirname "$0")"; pwd)
hive_config=$(cat  "${CUR_DIR}"/hive.config)
sql="${hive_config}${sql_suffix}"
echo "导入trip_statistics_temp的SQL为：${sql}"

#执行语句·
hive -e "${sql}"
if [ $? -ne 0 ];then
  echo "导入trip_statistics_temp失败，耗时$((`date +%s`-START_TIME))秒..."
else
  echo "导入trip_statistics_temp成功，耗时$((`date +%s`-START_TIME))秒..."
fi